import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;

public class importMain {

	public static void main(String[] args) {
		System.out.println("Tool for importing from textfile to SQL-db.\n");
		
		importArticles("artno.txt", "artname.txt", "artpa.txt");
	}
	
	private static void importArticles(String fileArtNo, String fileArtName, String fileArtPA) {
		System.out.println("Importing ArtNo's from " + fileArtNo + ", ArtNames from " + fileArtName + " and ArtPA from " + fileArtPA + ".");
		
		try {
			BufferedReader bfArtNo = new BufferedReader(new FileReader(fileArtNo));
			String strArtNo = bfArtNo.readLine();
			BufferedReader bfArtName = new BufferedReader(new FileReader(fileArtName));
			String strArtName = bfArtName.readLine();
			BufferedReader bfArtPA = new BufferedReader(new FileReader(fileArtPA));
			String strArtPA = bfArtPA.readLine();
			
			while (strArtNo != null) {
				addToSQL(strArtNo, strArtName, strArtPA);
				strArtNo = bfArtNo.readLine();
				strArtName = bfArtName.readLine();
				strArtPA = bfArtPA.readLine();
			}
			
			bfArtNo.close();
			bfArtName.close();
			bfArtPA.close();
		}
		catch (FileNotFoundException e) {
			System.out.println("File not found.");
			e.printStackTrace();
		}
		catch (IOException e) {
			System.out.println("File could not be opened.");
			e.printStackTrace();
		}
	}
	
	private static void addToSQL(String artNo, String artName, String artPA) {
		try {
			System.out.print("Connecting to SQL-Server... ");
			Connection myConn;
			Class.forName("com.mysql.jdbc.Driver");
			myConn = DriverManager.getConnection("jdbc:mysql://192.168.1.35:3306/artdb","juser","jpass");
			System.out.println("Connected.");
			
			System.out.print("Adding " + artNo + " " + artName + " " + artPA + " to SQL-DB... ");
			
			Statement myStatement = myConn.createStatement();
			//System.out.println("INSERT INTO `artdb`.`articles` (`id`, `artno`, `artname`) VALUES (NULL, '" + artNo + "', '" + artName + "');");
			myStatement.execute("INSERT INTO `artdb`.`articles` (`id`, `artno`, `artname`, `artpa`) VALUES (NULL, '" + artNo + "', '" + artName + "', '" + artPA + "');");
			
			System.out.println("Done.");
			
			myConn.close();
			System.out.println("Connection closed.");
		}
		catch (Exception e) {
			System.out.println("Connection failed.");
			e.printStackTrace();
		}
	}
}